import * as xlsx from 'xlsx';
import * as fs from 'fs';
import * as path from 'path';

function excelDetail(filename: string): Promise<any[]> {
    return new Promise((resolve, reject) => {
        try {
            const filePath = path.resolve(filename);
            const fileBuffer = fs.readFileSync(filePath); // Read file as buffer
            const workbook = xlsx.read(fileBuffer, { type: 'buffer' }); // Read buffer as workbook
            const sheetName = workbook.SheetNames[0]; // Get the first sheet name
            const worksheet = workbook.Sheets[sheetName];
            // Convert sheet to JSON
            const jsonData = xlsx.utils.sheet_to_json(worksheet, { header: 1 });
            const resData = transformData(jsonData);
            resolve(resData);
        } catch (error) {
            reject(error);
        }
    });
}

// 数据转换，读取到的第一行数据是标题，后面是数据，现在转换为对象数组
function transformData(data: any[]) {
    const headers = data[0];
    const rows = data.slice(1);
    const transformedData = rows.map((row) => {
        const obj = {};
        headers.forEach((header, index) => {
            let cellValue = row[index];
            // 检查并转换日期序列号
            if (typeof cellValue === 'number' && isExcelDate(cellValue)) {
                cellValue = convertExcelDate(cellValue);
            }
            obj[header] = cellValue;
        });
        return obj;
    });
    // 根据 threshold 过滤大多数项为空的对象
    return transformedData.filter(obj => {
        const totalFields = Object.keys(obj).length;
        const emptyFields = Object.values(obj).filter(value => value === undefined || value === '').length;
        const emptyRatio = emptyFields / totalFields;
        return emptyRatio <= 0.5; // 保留空值比例小于等于阈值的数据
    });
}

// 判断是否为Excel日期序列号
function isExcelDate(value: number): boolean {
    return value > 25569; // 基于1900年1月1日的天数
}

// 将Excel日期序列号转换为JS Date对象并格式化为字符串
function convertExcelDate(value: number): string {
    const date = new Date((value - 25569) * 86400 * 1000);
    return date.toISOString().replace('T', ' ').slice(0, 19); // 格式化日期
}

export default excelDetail;
